﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using DBUtility;
using IDAL;
using Model;

namespace DAL
{
    public class PCBAging:IPCBAging
    {
        #region 静态变量声明与赋值
        private const string PARM_PCBAGING_ID = "@PCBAgingID";
        private const string PARM_PCBAGING_USERID = "@UserID";
        private const string PARM_PCBAGING_PCBTYPEID = "@PCBTypeID";
        private const string PARM_PCBAGING_FIRMWAREVERSION = "@FirmwareVersion";
        private const string PARM_PCBAGING_AMOUNT = "@Amount";

        private const string PARM_PCBAGING_INBOXUSER = "@InBoxUser";
        private const string PARM_PCBAGING_INBOXU = "@InBoxU";
        private const string PARM_PCBAGING_INBOXI = "@InBoxI";

        private const string PARM_PCBAGING_OUTBOXUSER = "@OutBoxUser";
        private const string PARM_PCBAGING_OUTBOXU = "@OutBoxU";
        private const string PARM_PCBAGING_OUTBOXI = "@OutBoxI";

        private const string PARM_PCBAGING_DT = "@PCBAgingItemdt";

        private const string PARM_PCBAGING_STARTTIME = "@startTime";
        private const string PARM_PCBAGING_STOPTIME = "@stopTime";

        private const string PARM_PCBAGING_PCBSN = "@PCBSN";
        #endregion
        
        #region 产线操作部分
        //事务可能涉及多表的操作，在事务执行过程中，所涉及的所有表都不能进行查询、更新等操作，只待事务提交或者回滚之后，才释放这种占有权。
        //如果你将这个测试用例用断点断住，你会发现，每当操作到一张表的时候，事务就将夺得这张表使用权。
        public bool PCBAgingAndItem_Insert(PCBAgingInfo ai, DataTable dt)
        {
            List<SqlParameter> pList = new List<SqlParameter>() {
            sqlHelper.CreateSqlParemeterStructured(PARM_PCBAGING_DT,dt)};

            SqlParameter[] testParms = GetParameters();
            testParms[0].Value = ai.PCBAgingID;
            testParms[1].Value = ai.UserID;
            testParms[2].Value = ai.PCBTypeID;
            testParms[3].Value = ai.FirmwareVersion;
            testParms[4].Value = ai.Amount;

            using (SqlConnection conn = new SqlConnection(sqlHelper.ConnectionStringLocalTransaction))
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                SqlTransaction tran = conn.BeginTransaction("SampleTransaction1");
                cmd.Connection = conn;
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.StoredProcedure;
                try
                {
                    foreach (SqlParameter parm in testParms)
                        cmd.Parameters.Add(parm);
                    cmd.CommandText = "PCBAging_Insert";
                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    foreach (SqlParameter parm in pList)
                        cmd.Parameters.Add(parm);
                    cmd.CommandText = "PCBAgingItem_Insert";
                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    tran.Commit();
                    return true;
                }
                catch
                {
                    tran.Rollback();
                    return false;
                }
            }
        }

        public bool PCBAging_InBox(string pCBAgingID, string userid, decimal inBoxU,decimal inBoxI)
        {
            try
            {
                SqlParameter[] testParms = Get4Parameters(PARM_PCBAGING_INBOXUSER, PARM_PCBAGING_INBOXU, PARM_PCBAGING_INBOXI);
                //Bind the parameter
                testParms[0].Value = pCBAgingID;
                testParms[1].Value = userid;
                testParms[2].Value = inBoxU;
                testParms[3].Value = inBoxI;
                SqlCommand cmd = new SqlCommand();
                foreach (SqlParameter parm in testParms)
                    cmd.Parameters.Add(parm);
                using (SqlConnection conn = new SqlConnection(sqlHelper.ConnectionStringLocalTransaction))
                {
                    conn.Open();
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "PCBAging_InBox";
                    using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { }
                }
                cmd.Parameters.Clear();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public bool PCBAging_OutBox(string pCBAgingID, string userid, decimal outBoxU, decimal outBoxI)
        {
            try
            {
                SqlParameter[] testParms = Get4Parameters(PARM_PCBAGING_OUTBOXUSER, PARM_PCBAGING_OUTBOXU, PARM_PCBAGING_OUTBOXI);
                //Bind the parameter
                testParms[0].Value = pCBAgingID;
                testParms[1].Value = userid;
                testParms[2].Value = outBoxU;
                testParms[3].Value = outBoxI;
                SqlCommand cmd = new SqlCommand();
                foreach (SqlParameter parm in testParms)
                    cmd.Parameters.Add(parm);
                using (SqlConnection conn = new SqlConnection(sqlHelper.ConnectionStringLocalTransaction))
                {
                    conn.Open();
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "PCBAging_OutBox";
                    using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { }
                }
                cmd.Parameters.Clear();
                return true;
            }
            catch
            {
                return false;
            }
        }
        #endregion

        #region 数据检索部分
        //-------------------------以上为老化数据写入，以下为老化数据检出-----------------------------------------------

        public bool PCBAging_ExistsID(string pCBAgingID)
        {
            SqlParameter parm = new SqlParameter(PARM_PCBAGING_ID, SqlDbType.NVarChar, 50);
            //Bind the parameter
            parm.Value = pCBAgingID;
            int n = Convert.ToInt32(sqlHelper.ExecuteScalar(sqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "PCBAging_ExistsID", parm));
            return Convert.ToBoolean(n);
        }
        
        public PCBAgingInfo GetbyPCBAgingID(string pCBAgingID)
        {
            PCBAgingInfo test;
            //Create a parameter    
            SqlParameter parm = new SqlParameter(PARM_PCBAGING_ID, SqlDbType.NVarChar, 50);
            //Bind the parameter
            parm.Value = pCBAgingID;

            //Execute the query	
            using (SqlDataReader rdr = sqlHelper.ExecuteReader(sqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "PCBAging_SelectbyPCBAgingID", parm))
            {
                if (rdr.Read())
                    test = new PCBAgingInfo(rdr.GetValue(0).ToString(), rdr.GetDateTime(1), rdr.GetValue(2).ToString(),
                        rdr.GetValue(3).ToString(), rdr.GetValue(4).ToString(), rdr.GetInt32(5), rdr.GetDateTime(6), rdr.GetValue(7).ToString(), 
                        rdr.GetDecimal(8), rdr.GetDecimal(9), rdr.GetDateTime(10), rdr.GetValue(11).ToString(), rdr.GetDecimal(12),
                        rdr.GetDecimal(13));
                else
                    test = new PCBAgingInfo();
            }
            return test;
        }

        public IList<PCBAgingInfo> GetLatestPCBAging()
        {
            IList<PCBAgingInfo> tests = new List<PCBAgingInfo>();

            SqlParameter[] testParms = GetDTParameters();
            //Bind the parameter
            testParms[0].Value = DateTime.Now.AddDays(-3);
            testParms[1].Value = DateTime.Now;

            //Execute a query to read the categories
            using (SqlDataReader rdr = sqlHelper.ExecuteReader(sqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "PCBAging_SelectbyDT", testParms))
            {
                while (rdr.Read())
                {
                    PCBAgingInfo test = new PCBAgingInfo(rdr.GetValue(0).ToString(), rdr.GetDateTime(1), rdr.GetValue(2).ToString(),
                        rdr.GetValue(3).ToString(), rdr.GetValue(4).ToString(), rdr.GetInt32(5), rdr.GetDateTime(6), rdr.GetValue(7).ToString(),
                        rdr.GetDecimal(8), rdr.GetDecimal(9), rdr.GetDateTime(10), rdr.GetValue(11).ToString(), rdr.GetDecimal(12),
                        rdr.GetDecimal(13));
                    tests.Add(test);
                }
            }
            return tests;
        }

        public IList<PCBAgingInfo> GetPCBAgingsbyDT(DateTime starttime, DateTime stoptime)
        {
            IList<PCBAgingInfo> tests = new List<PCBAgingInfo>();

            SqlParameter[] testParms = GetDTParameters();
            //Bind the parameter
            testParms[0].Value = starttime;
            testParms[1].Value = stoptime;

            using (SqlDataReader rdr = sqlHelper.ExecuteReader(sqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "PCBAging_SelectbyDT", testParms))
            {
                while (rdr.Read())
                {
                    PCBAgingInfo test = new PCBAgingInfo(rdr.GetValue(0).ToString(), rdr.GetDateTime(1), rdr.GetValue(2).ToString(),
                        rdr.GetValue(3).ToString(), rdr.GetValue(4).ToString(), rdr.GetInt32(5), rdr.GetDateTime(6), rdr.GetValue(7).ToString(),
                        rdr.GetDecimal(8), rdr.GetDecimal(9), rdr.GetDateTime(10), rdr.GetValue(11).ToString(), rdr.GetDecimal(12),
                        rdr.GetDecimal(13));
                    tests.Add(test);
                }
            }
            return tests;
        }

        public string GetIDbySN(string pCBSN)
        {
            SqlParameter parm = new SqlParameter(PARM_PCBAGING_PCBSN, SqlDbType.NVarChar, 50);
            parm.Value = pCBSN;
            string str = Convert.ToString(sqlHelper.ExecuteScalar(sqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "PCBAgingID_SelectbySN", parm));
            return str;
        }
        #endregion

        #region 参数部分
        //-------------------------ADO.NET参数部分----------------------------------------------------------------------
        private static SqlParameter[] GetDTParameters()
        {
            SqlParameter[] parms = null;
            parms = new SqlParameter[]
                {
					new SqlParameter(PARM_PCBAGING_STARTTIME, SqlDbType.DateTime, 8),
					new SqlParameter(PARM_PCBAGING_STOPTIME, SqlDbType.DateTime, 8)
                };
            return parms;
        }
        
        private static SqlParameter[] GetParameters()
        {
            SqlParameter[] parms = null;
            parms = new SqlParameter[]
            {
                new SqlParameter(PARM_PCBAGING_ID,SqlDbType.NVarChar,50),
                new SqlParameter(PARM_PCBAGING_USERID,SqlDbType.NVarChar,10),
                new SqlParameter(PARM_PCBAGING_PCBTYPEID,SqlDbType.NVarChar,50),
                new SqlParameter(PARM_PCBAGING_FIRMWAREVERSION,SqlDbType.NVarChar,20),
                new SqlParameter(PARM_PCBAGING_AMOUNT,SqlDbType.Int,4)
            };
            return parms;
        }
        
        private static SqlParameter[] Get4Parameters(string UserID,string BoxU,string BoxI)
        {
            SqlParameter[] parms = null;
            parms = new SqlParameter[]
                {
					new SqlParameter(PARM_PCBAGING_ID, SqlDbType.NVarChar, 50),
					new SqlParameter(UserID, SqlDbType.NVarChar, 10),
                    new SqlParameter(BoxU,SqlDbType.Decimal,4),
                    new SqlParameter(BoxI,SqlDbType.Decimal,4)
                };
            return parms;
        }
        #endregion
    }
}
